package DBLayer;
import GUILayer.MainGUI;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Customer;
import ModelLayer.CustomerGroup;

public class DBCustomer implements IFDBCustomer
{
    private Connection con;
    
    public DBCustomer()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    @Override
    public Customer findCustomer(int customerID, boolean retriveAssociation)
    {
        Customer customerObj = new Customer();
        customerObj = singleWhere("cId = " + customerID, retriveAssociation);
        
        return customerObj;
    }
    
    @Override
    public Customer findCustomerByPhone(int phoneNo, boolean retriveAssociation)
    {
        Customer customerObj = new Customer();
        customerObj = singleWhere("phoneNo = " + phoneNo, retriveAssociation);
        
        return customerObj;
    }
    
    @Override
    public Customer findCustomerByName(String fname, boolean retriveAssociation)
    {
        Customer customerObj = new Customer();
        customerObj = singleWhere("fname = " + fname , retriveAssociation);
        
        return customerObj;
    }
   
    private Customer singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Customer customerObj = new Customer();
        String query =  buildQuery(wClause);
        
        try
        {
	   Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
           
	   if(results.next())
           {
	       customerObj = buildCustomer(results);
           }
           
	   stmt.close();
	}
	catch(Exception e)
        {
	   
        }
        
	return customerObj;
    }
    
    private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Customer> list = new ArrayList<Customer>();

        String query =  buildQuery(wClause);
        
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while(results.next())
            {
                 Customer customerObj = new Customer();
                 customerObj = buildCustomer(results);	
                 list.add(customerObj);
            }

            stmt.close();
        }
        catch(Exception e)
        {
            
        }

        return list;
    }
    
    private Customer buildCustomer(ResultSet results)
    {
        Customer customerObj = new Customer();
        
        try
        {
           customerObj.setId(results.getInt("cId")); // Skal resultaterne være i en bestemt rækkefølge, eller er det ligemeget?
           customerObj.setFname(results.getString("fName"));
           customerObj.setLname(results.getString("lName"));
           customerObj.setAddress(results.getString("address")); 
           customerObj.setZipCode(results.getInt("zipCode"));
           customerObj.setCity(results.getString("city"));
           customerObj.setDiscount(results.getDouble("discount"));
           customerObj.setEmail(results.getString("email"));
      //   customerObj.setCvr(results.getInt(6));
           customerObj.setPhoneNo(results.getInt("phoneNo"));
          
       //  customerObj.setCusGroup(null); // Skal lige fixes

                }
        catch(Exception e)
        {
            
        }
        
        return customerObj;
    }
 
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Customer";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query;
    }

    @Override
    public ArrayList getAllCustomer(boolean retriveAssociation) 
    {
        ArrayList list = new ArrayList();
        list = miscWhere("", retriveAssociation);
        return list;
     }

    @Override
    public void insertCustomer(Customer customerObj)
    {  

        String query = "INSERT INTO Customer ( fName, lName, address, zipCode, city, email, discount, phoneNo) "
                + "VALUES(" +
                "'"+ customerObj.getFname() +"', " +
                "'"+ customerObj.getLname() +"', " +
                "'"+ customerObj.getAddress() +"', " +
                " "+ customerObj.getZipCode() +", " + 
                "'"+ customerObj.getCity() +"', " +
                "'"+ customerObj.getEmail() +"', " +
                ""+ customerObj.getDiscount()+ ", " +
                ""+ customerObj.getPhoneNo()+") "; 
          
        
        // customerGroupID skal kunne være null
                   // "customerGroupType ='" + customerObj.getCusGroup().getType() + "','" +
      
        System.out.println("Update query: " + query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            
            
            stmt.close();
        }
        catch(Exception e)
        {
            
        }
    }



    public void updateCustomer (Customer customerObj)
    {

        String query="UPDATE Customer SET " +
                "fName ='"+ customerObj.getFname() + "', " +
                "lName ='"+ customerObj.getLname() + "', " +
                "address = '" + customerObj.getAddress() + "', " +
                "city ='"+ customerObj.getCity() + "', " +
                "email ='"+ customerObj.getEmail() + "', " +
               
              //  "cvr ='"+ customerObj.getCvr() + "', " +
                "discount ='"+ customerObj.getDiscount() + "', " +
                "phoneNo ='"+ customerObj.getPhoneNo() + "', " +
                "zipCode ='"+ customerObj.getZipCode() + "' " +
              //  "customerGroupID ='" + customerObj.getCusGroup().getCustomerGroupID() +
                "WHERE cId = '" + customerObj.getId() + "';";
        
         System.out.println("Update query:" + query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception e)
        {
            
        }
    }

    @Override
    public void deleteCustomer(int customerID)
    {
        String query = "DELETE FROM Customer WHERE cId = '" +
                        customerID + "'";

        try
        { 
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                stmt.executeUpdate(query);
                stmt.close();
        }
        catch(Exception e)
        {
            
        }
    }
 


    
   @Override
    public int MaxID()
    {
        String query = "SELECT max(cID) FROM Customer";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading max id: " + e);
        } 
        return id;     
    }

     @Override
    public int Antal() {
    String query = "SELECT COUNT(*) FROM Customer";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading count: " + e);
        } 
        return id;   
    }
}